package dm;
import java.sql.CallableStatement;
import java.sql.Connection;
import bean.user.SessionUser;
import oracle.jdbc.OracleTypes;

import database.DBConnection;


public class UserDM {
	
	public SessionUser checkUser(String userName,String password) throws Exception{
	
		Connection con = DBConnection.getConnection();
		CallableStatement statement = null;
		SessionUser user = null;

		try {
			statement = con.prepareCall("{?=call USER_PKG.USER_REG(?,?)}");
			statement.registerOutParameter( 1, OracleTypes.NUMBER );
			System.out.println("u :"+userName+" p :"+password);
			statement.setString(2,userName);
			statement.setString(3, password);
			statement.execute();
			user = new SessionUser(userName,password,statement.getInt(1));
			
			DBConnection.closeConnection(con);
			return user;
			
		} catch (Exception e) {
			try {
				statement.close();
			} catch (Exception exception) {
			}
			try {
				DBConnection.closeConnection(con);
			} catch (Exception exception) {
			}
			throw e;
		}
	}

	
	public boolean changePassword(String userName,String password,String newPassword) throws Exception{
		Connection con = DBConnection.getConnection();
		CallableStatement statement = null;
		try {
			System.out.println("UUU :"+userName+" pppp:"+password+" pasnew:"+newPassword);
			statement = con.prepareCall("{?=call USER_PKG.USER_PASS_CHANGE(?,?,?)}");
			statement.registerOutParameter(1, OracleTypes.NUMBER);
			statement.setString(2,userName);
			statement.setString(3, password);
			statement.setString(4, newPassword);
			statement.execute();
			boolean ctrl = (statement.getInt(1)== 1);
			DBConnection.closeConnection(con);
			return ctrl;
			
		} catch (Exception e) {
			try {
				statement.close();
			} catch (Exception exception) {
			}
			try {
				DBConnection.closeConnection(con);
			} catch (Exception exception) {
			}
			throw e;
		}
	}
	
	public boolean deactivateUser(String userName,String password) throws Exception{
		Connection con = DBConnection.getConnection();
		CallableStatement statement = null;

		try {
			statement = con.prepareCall("{?=call USER_PKG.DEACTIVATE_USER(?,?)}");
			statement.registerOutParameter(1, OracleTypes.NUMBER);
			statement.setString(2,userName);
			statement.setString(3,password);
			statement.execute();
			boolean ctrl = (statement.getInt(1)== 1);
			DBConnection.closeConnection(con);
			return ctrl;
			
		} catch (Exception e) {
			try {
				statement.close();
			} catch (Exception exception) {
			}
			try {
				DBConnection.closeConnection(con);
			} catch (Exception exception) {
			}
			throw e;
		}
	}
	
	public SessionUser createUser(String userName,String password,String password2,String name,String tel,String mail) throws Exception{
		
		Connection con = DBConnection.getConnection();
		CallableStatement statement = null;
//		ResultSet res = null;
		SessionUser user = null;

		try {
			if(password.equals(password2)){
				
				statement = con.prepareCall("{?=call USER_PKG.CREATE_USER(?,?,?,?,?)}");
				statement.registerOutParameter(1, OracleTypes.NUMBER );
				statement.setString(2,userName);
				statement.setString(3,password);
				statement.setString(4, name);
				statement.setString(5, tel);
				statement.setString(6, mail);
				statement.execute();
				int id = 0;
				
				if((id = statement.getInt(1)) != 0){
					user = new SessionUser(userName, password, id);
				
				statement.close();
				DBConnection.closeConnection(con);
				
				return user;
				}
			}
			
		} catch (Exception e) {
			try {
				statement.close();
			} catch (Exception exception) {
			}
			try {
				DBConnection.closeConnection(con);
			} catch (Exception exception) {
			}
			throw e;
		}
		return null;
	}
	
	public boolean updateUser(SessionUser user,String userName,String pass,String name,String tel,String mail) throws Exception{
		Connection con = DBConnection.getConnection();
		CallableStatement statement = null;

		try {
			System.out.println("geldm :"+user.getPassword()+" pass: "+pass);
			if(user.getPassword().equals(pass)){
				statement = con.prepareCall("{?=call USER_PKG.UPDATE_USER(?,?,?,?,?,?,?)}");
				statement.registerOutParameter(1, OracleTypes.NUMBER);
				statement.setInt(2,user.getUserId());
				statement.setString(3,userName);
				statement.setString(4,user.getPassword());
				statement.setString(5, pass);
				statement.setString(6, name);
				statement.setString(7, tel);
				statement.setString(8, mail);
				statement.execute();
				if(statement.getInt(1) == 1){
					statement.close();
					DBConnection.closeConnection(con);
					return true;
				}
				statement.close();
			}
			DBConnection.closeConnection(con);
			
		} catch (Exception e) {
			try {
				statement.close();
			} catch (Exception exception) {
			}
			try {
				DBConnection.closeConnection(con);
			} catch (Exception exception) {
			}
			throw e;
		}
		return false;
	}

}
